﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SQLite;

namespace DMSClient.General
{
    public partial class ErrorManager : Form
    {
        List<string> keyWorks = new List<string>();
        private string iName = "";
        private string eName = "";
        DataTable dtProcess;

        public ErrorManager()
        {
            InitializeComponent();
            ugData.DisplayLayout.CaptionVisible = Infragistics.Win.DefaultableBoolean.True;
            centrarLoader();
        }

        private void ubInterface_Click(object sender, EventArgs e)
        {
            System.IO.StreamReader srInterface;
            ofdUpload.Title = "Select the Interface file";
            ofdUpload.Filter = "Text File (*.txt)|*.txt";
            string[] columnas;
            DataTable dt = new DataTable();
            
            if (ofdUpload.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                ugData.DataSource = null;
                iName = "";
                System.IO.FileInfo fi = new System.IO.FileInfo(ofdUpload.FileName);
                iName = fi.Name;
                if (eName.Trim().Equals(""))
                {
                    ugData.Text = "Interface: " + iName;
                }
                else
                {
                    ugData.Text = "Interface: " + iName + "\nError File: " + eName;
                }
                string header = "";
                try{
                
                switch(iName.ToLower().Substring(0,iName.IndexOf("_"))){
                    case "importorgunit":
                        header = "cdOrgUnit	cdRegion	dsName	dsCorporateName	cdLegalCode1	cdLegalCode2	cdLegalCode3	cdStatus	nmAddress	nrAddress	dsAddressComplement	nrZipCode	nmNeighborhood	cdAdministrativeRegion	cdCity	nmFirstName	nmLastName	dsJob	eMail	nrDDD	nrPhone	nrExtPhone	IntegrationPassword";
                        break;
                    case"importunit":
                        header = "cdUnit	nmUnit	nmUnitShort	cdStatus";
                        break;
                    case"importclassification":
                        header ="cdClassification	cdClassificationType	nmClassification	nrLevel";
                        break;
                    case"importclassificationHierarchy":
                        header ="cdParentClassification	cdChildClassification	cdClassificationType";
                        break;
                    case"importproduct":
                        header="cdProduct	cdStatus	dsProduct	cdClass1	nmClass1	cdClass2	nmClass2	cdClass3 	nmClass3	cdClass4	nmClass4	cdClass5	nmClass5	cdClass6	nmClass6	cdClass7	nmClass7	cdClass8	nmClass8	cdUnit	cdEAN	cdVolumeUnit	vlConversionUnit	vlConversionKGLT";
                        break;
                    case"importstatecity":
                        header="cdCity	nmCity	cdState	nmState";
                        break;
                    case"importcabinet":
                        header="cdCabinet	dsCabinet	cdStatus	cdCabinetType";
                        break;
                    case"importregionuser":
                        header="cdRegion	cdRegionType	dsRegion	cdRegionStatus	cdParentRegion	cdUser	nmFirstName	nmLastName	nrPhone1	nrPhone2	Email	cdUserStatus";
                        break;
                    case"importquota":
                        header="cdQuotaDef	cdQuota	cdOrgUnit	cdStoreClassif	cdRegion	cdProductClassif	cdProduct	vlQuota1	vlQuota2	vlQuota3	dtMonthYear	cdStatus";
                        break;
                    case"importstore":
                        header="cdStore	cdStatus	cdStoreBrand	dsName	dsCorporateName	cdClass1	cdClass2	cdCity	cdRegion	nmAddress	nrAddress	dsAddressComplement	nrZipCode	nmNeighborhood";
                        break;
                    case"importcabinetinventory":
                        header="cdCabinet	cdStore	nrEquipement	cdStatus	dtInventory";
                        break;
                    case"importstock":
                        header="cdProduct	cdWarehouse	vlBeginningMonthAmount	vlBeginningMonthQuantity	vlReceivedQuantity	vlDispatchedQuantity	dtStock	cdStatus";
                        break;
                    case"importvisit":
                        header="cdVisitInstance	cdRegion	cdStore	cdUser	dtStart	cdStatus";
                        break;
                    case"importroute":
                        header="cdRoute	nmRoute	cdStatus";
                        break;
                    case"importorderinvoice":
                        header="cdOrder	cdCreationUser	cdStore	cdRegion	dtOrder	vlDiscountTotal	vlTotalOrder	vlTotalUnit	cdRoute	cdStatusOrder	cdInvoice	dsDisplayText	dtInvoice 	vlTotalInvoice	nrTotalQuantity	cdStatusInvoice";
                        break;
                    case"importorderiteminvoiceitem":
                        header="cdOrder	cdProduct	nrSequenceOrder	nrQuantityOrder 	vlDiscountSubtotal	vlSubtotalOrder	cdInvoice	nrSequenceInvoice	nrQuantityInvoice	vlSubtotalInvoice";
                        break;
                    case "importccfot":
                        header="cdCCFOT	cdStore	cdProduct	vlOrdQt	vlInvQt	vlDelQtOnTime	vlRetQt	vlCancQt 	vlCancCNQt	dtCCFOT 	cdStatus";
                        break;
                    default:
                        MessageBox.Show(this, "Please verify the file name", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                }
                }catch(Exception ex){
                    MessageBox.Show(this, "Please verify the file name", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                }

                SQLiteCommand sqlcmd = new SQLiteCommand();
                sqlcmd.Connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "\\DMSToolsClientDB.sqlite;Password=Nu&<0.$U><,+N.|uAi73+X~vkqj~?_fh6Wgtdq1&+Kfk|{VF]hbE&lVD6&M-7jeD");
                sqlcmd.CommandType = CommandType.Text;
                try
                {
                    sqlcmd.Connection.Open();
                    sqlcmd.CommandText = "DROP TABLE IF EXISTS interface";
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    //throw new Exception("Unable to create temp db file.", ex.InnerException);
                    MessageBox.Show(this, "Unable to create temp db file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                string query = "CREATE TABLE IF NOT EXISTS \"interface\" (";
                srInterface = new System.IO.StreamReader(ofdUpload.FileName, System.Text.Encoding.Default, true);
                string[] linea = header.Split((char)Keys.Tab);
                columnas = linea;
                foreach (string col in linea) {
                    query += "\""+col+"\" VARCHAR,";
                }
                query = query.Substring(0, query.Length - 1) + ")";
                try
                {
                    sqlcmd.CommandText = query;
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(this, "Please verify that the interface have a header", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    if (eName.Equals(""))
                    {
                        ugData.Text = "";
                        //ugData.Text = "Interface: " + iName;
                    }
                    else
                    {
                        ugData.Text = "Error File: " + eName;
                    }
                    return;
                    //throw new Exception("please verify that the interface have a header", ex.InnerException);
                }
                string otro;

                try
                {
                    sqlcmd.Transaction = sqlcmd.Connection.BeginTransaction();
                    sqlcmd.CommandText = "DELETE FROM interface";
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    sqlcmd.Transaction.Rollback();
                    sqlcmd.Connection.Close();
                    //throw new Exception("Error trying to write to temporary database file.", ex.InnerException);
                    MessageBox.Show(this, "Error trying to write to temporary database file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    if (eName.Equals(""))
                    {
                        ugData.Text = "";
                        //ugData.Text = "Interface: " + iName;
                    }
                    else
                    {
                        ugData.Text = "Error File: " + eName;
                    }
                    return;
                }

                while ((otro = srInterface.ReadLine()) != null) {
                    linea = otro.Split((char)Keys.Tab);
                    query = "";
                    string temp = "insert into interface values(";
                    try
                    {
                        for (int i = 0; i < columnas.Length; i++)
                        {
                            temp += "'" + linea[i].Substring(linea[i].IndexOf("=") + 1).Replace("'","") + "',";
                        }
                    }
                    catch (Exception exc) {
                        //throw new Exception("Error trying to write to temporary database file.", ex.InnerException);
                        MessageBox.Show(this, "Please verify the file structure", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        iName = "";
                        if (eName.Equals(""))
                        {
                            ugData.Text = "";
                            //ugData.Text = "Interface: " + iName;
                        }
                        else
                        {
                            ugData.Text = "Error File: " + eName;
                        }
                        return;
                    }

                    temp = temp.Substring(0,temp.Length-1)+")";
                    query = temp;
                    sqlcmd.CommandText = query;
                    try
                    {
                        sqlcmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        sqlcmd.Transaction.Rollback();
                        sqlcmd.Connection.Close();
                        //throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
                        MessageBox.Show(this, "Error trying to write to temporary database file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        if (eName.Equals(""))
                        {
                            ugData.Text = "";
                            //ugData.Text = "Interface: " + iName;
                        }
                        else
                        {
                            ugData.Text = "Error File: " + eName;
                        }
                        return;
                    }
                }
                srInterface.Close();
                try
                {
                    sqlcmd.Transaction.Commit();
                }
                catch (Exception ex)
                {
                    sqlcmd.Connection.Close();
                    //throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
                    MessageBox.Show(this, "Error trying to write to temporary database file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                sqlcmd.Transaction = null;
            }            
        }

        private void ulError_Click(object sender, EventArgs e)
        {
            System.IO.StreamReader srError;
            ofdUpload.Title = "Select the Interface file";
            ofdUpload.Filter = "Text File (*.txt)|*.txt";
            DataTable dt = new DataTable();

            if (ofdUpload.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                ugData.DataSource = null;
                eName = "";
                System.IO.FileInfo fi = new System.IO.FileInfo(ofdUpload.FileName);
                if (iName.Equals(fi.Name))
                {
                    MessageBox.Show(this, "The file " + fi.Name + " is not the error file for " + iName, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                eName = fi.Name;
                if (iName.Trim().Equals(""))
                {
                    ugData.Text = "Error File: " + eName;
                }
                else
                {
                    ugData.Text = "Interfaces: " + iName + "\nError File: " + eName;
                }
                keyWorks.Clear();
                srError = new System.IO.StreamReader(ofdUpload.FileName, System.Text.Encoding.Default, true);
                string srErrorLine = srError.ReadLine();
                string otro;
                bool primera = true;


                SQLiteCommand sqlcmd = new SQLiteCommand();
                sqlcmd.Connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "\\DMSToolsClientDB.sqlite;Password=Nu&<0.$U><,+N.|uAi73+X~vkqj~?_fh6Wgtdq1&+Kfk|{VF]hbE&lVD6&M-7jeD");
                sqlcmd.CommandType = CommandType.Text;

                try
                {
                    sqlcmd.Connection.Open();
                    sqlcmd.CommandText = "DROP TABLE IF EXISTS error";
                    sqlcmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    //throw new Exception("Unable to create temp db file.", ex.InnerException);
                    MessageBox.Show(this, "Unable to create temp db file.", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    if (iName.Equals(""))
                    {
                        ugData.Text = "";
                    }
                    else
                    {
                        ugData.Text = "Interface: " + iName;
                    }
                    return;
                }

                string query = "CREATE TABLE IF NOT EXISTS \"error\" (";
                int cont = 0;
                while ((otro = srError.ReadLine()) != null)
                {
                    if (otro.IndexOf("cdErr=0") > 0) continue;
                    string[] linea = otro.Split((char)Keys.Tab);

                    if (primera)
                    {
                        if (linea[0].IndexOf("=") < 0)
                        {
                            MessageBox.Show(this, "Please verify the error file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }
                        try
                        {
                            for (int x = 0; x < linea.Length; x++)
                            {
                                query += "\"" + linea[x].Substring(0, linea[x].IndexOf("=")) + "\" VARCHAR,";
                                keyWorks.Add(linea[x].Substring(0, linea[x].IndexOf("=")));
                            }
                            query = query.Substring(0, query.Length - 1) + ")";
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(this, "Please verify the error file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            if (iName.Equals(""))
                            {
                                ugData.Text = "";
                            }
                            else
                            {
                                ugData.Text = "Interface: " + iName;
                            }
                            return;
                        }

                        try
                        {
                            sqlcmd.CommandText = query;
                            sqlcmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            //throw new Exception("Unable to create temp db file.", ex.InnerException);
                            MessageBox.Show(this, "Unable to create temp db file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                        try
                        {
                            sqlcmd.Transaction = sqlcmd.Connection.BeginTransaction();
                            sqlcmd.CommandText = "DELETE FROM error";
                            sqlcmd.ExecuteNonQuery();
                        }
                        catch (Exception ex)
                        {
                            sqlcmd.Transaction.Rollback();
                            sqlcmd.Connection.Close();
                            //throw new Exception("Error trying to write to temporary database file.", ex.InnerException);
                            MessageBox.Show(this, "Error trying to write to temporary database file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                            if (iName.Equals(""))
                            {
                                ugData.Text = "";
                            }
                            else
                            {
                                ugData.Text = "Interface: " + iName;
                            }
                            return;
                        }
                    }

                    string insert = "";
                    string temp = "insert into error values(";
                    foreach (string col in linea)
                    {
                        temp += "'" + col.Substring(col.IndexOf("=") + 1) + "',";
                    }
                    temp = temp.Substring(0, temp.Length - 1) + ")";
                    insert += temp;
                    sqlcmd.CommandText = insert;
                    try
                    {
                        sqlcmd.ExecuteNonQuery();
                        cont++;
                    }
                    catch (Exception ex)
                    {
                        sqlcmd.Transaction.Rollback();
                        sqlcmd.Connection.Close();
                        //throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
                        MessageBox.Show(this, "Error trying to write to temporary database file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        if (iName.Equals(""))
                        {
                            ugData.Text = "";
                        }
                        else
                        {
                            ugData.Text = "Interface: " + iName;
                        }
                        return;

                    }
                    primera = false;
                }
                srError.Close();
                if (cont > 0)
                {
                    try
                    {
                        sqlcmd.Transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        sqlcmd.Connection.Close();
                        //throw new Exception("Error trying to write to temporal database file.", ex.InnerException);
                        MessageBox.Show(this, "Error trying to write to temporary database file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        if (iName.Equals(""))
                        {
                            ugData.Text = "";
                        }
                        else
                        {
                            ugData.Text = "Interface: " + iName;
                        }
                        return;
                    }
                    sqlcmd.Transaction = null;

                }
                else
                {
                    MessageBox.Show(this, "The file doesn't have enough information", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    eName = "";
                    ugData.Text = "Interface: " + iName;
                    sqlcmd.Connection.Close();
                }
            }
        }

        private void cambio(bool estado)
        {
            ugbPannel.Enabled = estado;
            loader1.Visible = !estado;
            if (!estado) centrarLoader();
        }

        private void ultraButton1_Click(object sender, EventArgs e)
        {
            ugData.DataSource = null;
            if (iName.Equals(""))
            {
                MessageBox.Show(this, "Please select the interface", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                ubInterface.PerformClick();
                return;
            }
            if (eName.Equals(""))
            {
                MessageBox.Show(this, "Please select the error file", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                ubError.PerformClick();
                return;
            }
            if (iName.IndexOf(eName.Substring(0, eName.Length - 4)) < 0)
            {
                MessageBox.Show(this, "The file " + eName + " is not the error file for " + iName, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            cambio(false);
            bgwProcess.RunWorkerAsync();
        }

        private void ubExport_Click(object sender, EventArgs e)
        {
            if (ugData.Rows.Count < 1)
            {
                MessageBox.Show(this, "There is no data to export", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            System.Windows.Forms.SaveFileDialog dialogo = new System.Windows.Forms.SaveFileDialog();
            dialogo.DefaultExt = "xls";
            Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter Exporter = new Infragistics.Win.UltraWinGrid.ExcelExport.UltraGridExcelExporter();
            if (dialogo.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                Exporter.Export(ugData, dialogo.FileName);
                if (MessageBox.Show(this, "Do you want to open the file?", this.Text, MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    System.Diagnostics.Process.Start(dialogo.FileName);
                }
            }
        }

        private void ErrorManager_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (bgwProcess.IsBusy) bgwProcess.CancelAsync();
            if (bgwUploadError.IsBusy) bgwUploadError.CancelAsync();
            if (bgwUploadInterface.IsBusy) bgwUploadInterface.CancelAsync();
        }

        private void centrarLoader()
        {
            loader1.Left = (this.Width - loader1.Width) / 2;
            loader1.Top = ((this.Height - loader1.Height) / 2) - loader1.Height;
        }

        private void bgwProcess_DoWork(object sender, DoWorkEventArgs e)
        {
            dtProcess = new DataTable();
            BackgroundWorker worker = sender as BackgroundWorker;
            SQLiteCommand sqlcmd = new SQLiteCommand();
            sqlcmd.Connection = new SQLiteConnection("Data Source=" + Application.StartupPath + "\\DMSToolsClientDB.sqlite;Password=Nu&<0.$U><,+N.|uAi73+X~vkqj~?_fh6Wgtdq1&+Kfk|{VF]hbE&lVD6&M-7jeD");
            sqlcmd.CommandType = CommandType.Text;
            string query = "select e.cdErr,e.dsErr,i.* from error e inner join interface i on ";
            keyWorks.Remove("cdErr");
            keyWorks.Remove("dsErr");
            foreach (string campo in keyWorks)
            {
                query += "e." + campo + "=i." + campo + " and ";
            }
            query = query.Substring(0, query.Length - 4);
            DataTable dt = new DataTable();
            try
            {
                sqlcmd.Connection.Open();
                sqlcmd.CommandText = query;
                SQLiteDataAdapter sqlda = new SQLiteDataAdapter(sqlcmd);
                sqlda.Fill(dtProcess);
                sqlcmd.Connection.Close();
                if (worker.CancellationPending)
                {
                    e.Cancel = true;
                    return;
                }
            }
            catch (Exception ex)
            {
                sqlcmd.Connection.Close();
                throw new Exception("Error trying to read from temporal database file.", ex.InnerException);
            }

        }

        private void bgwProcess_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            if (e.Error != null)
            {
                if (!this.IsDisposed)
                {
                    this.BringToFront();
                    MessageBox.Show(this, e.Error.Message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    cambio(true);
                }
            }
            else if (!e.Cancelled && !this.IsDisposed)
            {
                ugData.DataSource = dtProcess;
                cambio(true);
            }


        }
    }
}